import sqlalchemy

engine = sqlalchemy.create_engine('mysql://root:ZHabc123.@shc.chenzao.fun:13306/testdb', echo=True)

meta_data = sqlalchemy.MetaData()

department_table = sqlalchemy.Table(
    'department', meta_data,
    sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column('name', sqlalchemy.String(255), nullable=False, unique=True),
)

member_table = sqlalchemy.Table(
    'member', meta_data,
    sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True),
    sqlalchemy.Column('name', sqlalchemy.String(255)),
    sqlalchemy.Column('department_id', sqlalchemy.Integer, sqlalchemy.ForeignKey('department.id')),
)

meta_data.create_all(engine)


# 增加记录
# with engine.connect() as conn:
#     conn.execute(department_table.insert(), [
#       {"name": '财务部'}, {"name": "IT部"}
#     ])
#     conn.execute(member_table.insert(), [
#       {"name": "张三", "department_id": 1},
#       {"name": "王五", "department_id": 1},
#       {"name": "李四", "department_id": 2},
#       {"name": "赵六", "department_id": 2}
#     ])
#     conn.commit()

# 关联查询
with engine.connect() as conn:
    # 连接最后生成的其实是记录，把 join 想象成两张表连接起来构成的一张大表
    join = member_table.join(department_table, member_table.c.department_id == department_table.c.id)
    # 查询部门是财务部的所有成员的信息包含部门信息
    # query = sqlalchemy.select(join).where(department_table.c.name == '财务部')
    
    # 查询部门是财务部的所有员工信息，不要包含部门信息
    # query = sqlalchemy.select(member_table).select_from(join).where(department_table.c.name == '财务部')
    
    # 查询员工李四所在的部门
    query = sqlalchemy.select(department_table).select_from(join).where(member_table.c.name == '李四')
    result = conn.execute(query).fetchall()
    print(result)